package com.haizhi.crm.biz.dao;

import com.haizhi.crm.biz.model.VisitRecordDo;
import com.haizhi.crm.biz.model.VisitRecordResultVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by yangyijun on 2018/5/10.
 */
@Repository
public interface VisitRecordDao extends JpaRepository<VisitRecordDo, Long>, JpaSpecificationExecutor<VisitRecordDo> {

    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id, vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.customerKey = :customerKey  and vr.userId = u.id " )
    Page<VisitRecordResultVo> findByCustomerKey(@Param("customerKey" ) String customerKey, Pageable pageable);


    @Query(value = "select v from VisitRecordDo v where v.customerKey in :customerKeys" )
    List<VisitRecordDo> findBy(@Param("customerKeys" ) String[] customerKeys, Pageable pageable);

    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id, vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.outerEventKey = :outerEventKey  and vr.userId = u.id " )
    List<VisitRecordResultVo> findByOuterEventKey(@Param("outerEventKey" ) String outerEventKey);


    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id, vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.innerEventKey = :innerEventKey  and vr.userId = u.id " )
    List<VisitRecordResultVo> findByInnerEventKey(@Param("innerEventKey" ) String innerEventKey);

    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id, vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.productKey = :productKey and vr.userId = u.id  order by vr.visitTimeStart desc" )
    List<VisitRecordResultVo> findByProdutKey(@Param("productKey" ) String productKey);


    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id, vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.customerKey = :customerKey and  vr.userId = u.id  order by vr.visitTimeStart desc" )
    List<VisitRecordResultVo> queryByCustomerKey(@Param("customerKey" ) String customerKey);


    @Query("select customerKey from VisitRecordDo where userId in :userIds" )
    List<String> findCustomerKeysByUserIds(@Param("userIds" ) Long[] userIds);


    @Query(value = "select * from visit_record where inner_event_key = :innerKey order by visit_time_start desc limit :offsetValue , :limitValue", nativeQuery = true)
    List<VisitRecordDo> queryUserRelationByInnerEvent(@Param("innerKey" ) String innerKey, @Param("offsetValue" ) Integer offset, @Param("limitValue" ) Integer limit);

    @Query(value = "select count(1) from visit_record where inner_event_key = :innerKey",
            nativeQuery = true)
    Integer queryUserRelationByInnerEventCount(@Param("innerKey" ) String innerKey);


    @Query(value = "select * from visit_record where  outer_event_key = :outterKey order by visit_time_start desc limit :offsetValue , :limitValue",
            nativeQuery = true)
    List<VisitRecordDo> queryUserRelationByOutterEvent(@Param("outterKey" ) String outterKey, @Param("offsetValue" ) Integer offset, @Param("limitValue" ) Integer limit);

    @Query(value = "select count(1) from visit_record where outer_event_key = :outterKey",
            nativeQuery = true)
    Integer queryUserRelationByOutterEventCount(@Param("outterKey" ) String outterKey);

    @Query("select new com.haizhi.crm.biz.model.VisitRecordResultVo" +
            "(vr.id,vr.userId,vr.objectKey,vr.productKey,vr.innerEventKey,vr.outerEventKey,vr.customerKey,vr.title,vr.address,vr.recordDesc,vr.visitTimeStart,vr.visitTimeEnd,vr.updatedDt,vr.createdById,u.name,vr.visitorIds,vr.visitRecordId,vr.receiver) " +
            "from VisitRecordDo vr " +
            ", com.haizhi.crm.sys.auth.model.UserDo u " +
            "where vr.title LIKE CONCAT('%',:title,'%') and vr.userId = u.id  order by vr.visitTimeStart desc" )
    List<VisitRecordResultVo> queryByTitle(@Param("title" ) String title);

    VisitRecordDo findById(Long id);

    @Query(value = "select v from VisitRecordDo v where v.objectKey=:objectKey" )
    VisitRecordDo findByObjectKey(@Param("objectKey" ) String objectKey);

    @Query(value = "select * from tb_visit_record  where customer_key=:customerKey and object_key <>:objectKey order by visit_time_start desc limit 5", nativeQuery = true)
    List<VisitRecordDo> findBy(@Param("objectKey" ) String objectKey, @Param("customerKey" ) String customerKey);
}
